package cn.doitedu.olap.kylin;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class JdbcDemo {
    public static void main(String[] args) throws Exception {


        Class<?> cls = Class.forName("org.apache.kylin.jdbc.Driver");
        Driver driver = (Driver) cls.newInstance();

        Properties prop = new Properties();
        prop.setProperty("user", "ADMIN");
        prop.setProperty("password", "KYLIN");

        Connection connection = driver.connect("jdbc:kylin://doit01:7070/doitedu_action_analysis", prop);


        String sql = "with tmp as (select\n" +
                "\n" +
                "province,\n" +
                "city,\n" +
                "region,\n" +
                "count(distinct guid) as uv,\n" +
                "row_number() over(partition by province,city order by count(distinct guid) desc )   as rn\n" +
                "\n" +
                "from dwd.mall_app_pv_dtl2\n" +
                "where dt='2021-09-18'\n" +
                "group by province,city,region)\n" +
                "\n" +
                "select\n" +
                " province,\n" +
                " city,\n" +
                " region,\n" +
                " uv\n" +
                "from tmp\n" +
                "where rn<=3";


        PreparedStatement statement = connection.prepareStatement(sql);

        ResultSet resultSet = statement.executeQuery();

        System.out.println("province" + "\t" + "city" + "\t" + "region" +"\t" +"uv");
        while (resultSet.next()) {
            String province = resultSet.getString(1);
            String city = resultSet.getString(2);
            String region = resultSet.getString(3);
            long uv = resultSet.getLong("uv");
            System.out.println(province + "\t" + city + "\t" + region +"\t" +uv);

        }

        resultSet.close();
        statement.close();
        connection.close();
    }

}

